import pandas as pd

# 读取数据（假设文件名为000300perf.xlsx）
df = pd.read_excel('python\\sh000300\\000300perf_left.xlsx', sheet_name='000300perf')

# 提取必要列并处理日期格式
df = df[['日期Date', '收盘Close']]
df['日期Date'] = pd.to_datetime(df['日期Date'], format='%Y%m%d')
df = df.sort_values('日期Date')  # 按日期排序

base_investment = 60  # 基础定投金额
triggers = [0.5, 0.8, 1.0, 1.5, 2.0, 3.0]  # 跌幅触发点

for trigger in triggers:
    # 初始化变量
    cumulative_shares = 0.0    # 累计份额
    cumulative_investment = 0.0  # 累计投入
    results = []  # 存储每日结果

    # 逐日计算定投
    for i in range(len(df)):
        row = df.iloc[i]
        close_price = row['收盘Close']
        if i == 0:
            daily_investment = base_investment  # 第一天正常定投
        else:
            prev_close = df.iloc[i - 1]['收盘Close']
            change_rate = (close_price - prev_close) / prev_close * 100
            if change_rate <= -trigger:
                daily_investment = 2 * base_investment  # 满足触发条件，加倍定投
            else:
                daily_investment = base_investment

        daily_shares = daily_investment / close_price  # 当日买入份额
        cumulative_shares += daily_shares
        cumulative_investment += daily_investment

        current_value = cumulative_shares * close_price  # 当前市值
        profit = current_value - cumulative_investment   # 累计收益

        results.append({
            '日期': row['日期Date'].strftime('%Y-%m-%d'),
            '收盘价': close_price,
            '当日投入(元)': daily_investment,
            '当日买入份额': daily_shares,
            '累计投入(元)': cumulative_investment,
            '累计份额': cumulative_shares,
            '当前市值(元)': current_value,
            '累计收益(元)': profit,
            '收益率(%)': (profit / cumulative_investment) * 100 if cumulative_investment != 0 else 0
        })

    # 生成结果表格
    result_df = pd.DataFrame(results)

    # 输出到以触发条件命名的 Excel 文件
    file_name = f'定投收益结果_left_触发条件_{trigger}%.xlsx'
    result_df.to_excel(file_name, index=False, float_format='%.4f')

    # 打印最终结果
    final_profit = results[-1]['累计收益(元)']
    final_return = results[-1]['收益率(%)']
    print(f"触发条件 {trigger}% 的最终累计收益: {final_profit:.2f} 元")
    print(f"触发条件 {trigger}% 的总收益率: {final_return:.2f}%")